/**
 * author: xiexiaofei
 * data : 2017.3.2
 * update: 2017.3.29
 * sql辅助工具
 * 
 * 
 *  2017.3.29 : 增加insert和delete的集合操作
 * 
 *  
 */

var SQLHelper = function (table) {
    this.table = table
}

/**
 * 
 * 选择器
 * 
 * @param {array} whereData 关键字数组
 * @param {function} cb 回调函数
 * @param {string} orderCol 是否排序(默认降序)
 * @param {number} limitStart 
 * @param {number} limitEnd 
 */
SQLHelper.prototype.select = function (whereData, cb, offset, limit, orderby, sort) {
    // SELECT * FROM table where `score` = 1 order by `score` limite 0,10;
    var sql1 = 'SELECT * FROM ' + this.table + ' ';
    var sql2 = '';
    var sql3 = orderby ? ('order by ' + orderby + ' ' + sort + ' ') : ('');
    var sql4 = (offset > -1 && limit > 0) ? ('limit ' + offset + ',' + limit) : ('');

    var dataList = [];
    var sql2List = [];
    for (var i in whereData) {
        sql2List.push('`' + i + '` = ?');
        dataList.push(whereData[i]);
    }

    sql2 = sql2List.length ? (('where ' + sql2List.join(' and ')) + ' ') : ('');

    var sql = sql1 + sql2 + sql3 + sql4;

    this.query(sql, dataList, cb);

}

SQLHelper.prototype.insert = function (data, cb) {
    var sql1 = 'INSERT INTO ' + this.table + ' ';

    var sql2List = [];
    var dataList = [];
    var sql3List = [];

    for (let i in data) {
        sql2List.push('`' + i + '`');
        dataList.push(data[i]);
        sql3List.push('?');
    }

    var sql2 = '(' + sql2List.join(' , ') + ')';
    var sql3 = '(' + sql3List.join(', ') + ')';
    var sql = sql1 + sql2 + ' VALUES ' + sql3;
    // console.log(sql);
    this.query(sql, dataList, cb);


}

SQLHelper.prototype.insertSet = function (data, cb) {
    var sql1 = 'INSERT INTO ' + this.table + ' ';
    var sql2List = [];
    var dataList = [];

    // (`userid`, `userpw`)
    for (let i in data[0]) {
        sql2List.push('`' + i + '`');
    }

    var singleSql = [];
    var multipleSql = '';
    for (let i = 0; i < data.length; i++) {
        var item = data[i];
        for (let j in item) {
            singleSql.push('?');
            dataList.push(item[j]);
        }
        if (i != data.length - 1) { // 不是最后一个
            multipleSql += ('(' + singleSql.join(', ') + '), ')
        } else {
            multipleSql += ('(' + singleSql.join(', ') + ') ')  // ('?', '?'), ('?', '?');
        }
        singleSql = [];
    }
    var sql2 = '(' + sql2List.join(' , ') + ')';
    var sql = sql1 + sql2 + ' VALUES ' + multipleSql;
    // console.log(sql);
    this.query(sql, dataList, cb);

}

SQLHelper.prototype.update = function (where, data, cb) {
    var sql1 = 'UPDATE `' + this.table + '` SET '; // 'UPDATE `wechat_checkin` SET 
    var sql2List = [];
    var dataList = []; // 原始数据

    for (var i in data) {
        sql2List.push('`' + i + '` = ?');
        dataList.push(data[i]);
    }

    var sql2 = sql2List.join(', ') + ' where '; // `name`=?, `phone`=?, `idcard`=?, `address`=?, `remark`=? where `service_code` = ? and

    var sql2List2 = [];

    for (var i in where) {
        sql2List2.push('`' + i + '`=?');
        dataList.push(where[i]);
    }

    var sql3 = sql2List2.join(' and ') // `item_code` = ? and `name` = ? and `phone` = ?'

    var sql = sql1 + sql2 + sql3;

    this.query(sql, dataList, cb);
}

SQLHelper.prototype.updateSet = function (where, data, cb) {

    let max = 0;

    let checkOver = () => {
        max++;
        if (max == where.length) {
            return true;
        } else {
            return false;
        }
    }

    for (let i = 0; i < where.length; i++) {
        this.update(where[i], data[i], function (error, results, fields) {
            if (error) throw error;
            if (checkOver()) {
                cb(results);
            }
        })
    }
}

SQLHelper.prototype.delete = function (where, cb) {
    // SELECT * FROM table where `score` = 1 order by `score` limite 0,10;
    var sql1 = 'DELETE FROM ' + this.table + ' ';
    var sql2 = '';

    var dataList = [];
    var sql2List = [];

    for (var i in where) {
        sql2List.push('`' + i + '`=?');
        dataList.push(where[i]);
    }

    // (`service_code`='10001' and `userid`='oVVIUvxWr0WL3d_A8_zwNnN8FlYk')
    sql2 = '(' + sql2List.join(' and ') + ')'


    var sql = sql1 + 'where ' + sql2;

    this.query(sql, dataList, cb);
}

SQLHelper.prototype.deleteSet = function (where, cb) {
    // SELECT * FROM table where `score` = 1 order by `score` limite 0,10;
    var sql1 = 'DELETE FROM ' + this.table + ' ';
    var sql2 = '';

    var dataList = [];
    var sql2List = [];
    var multipleSql = [];
    for (let i = 0; i < where.length; i++) {
        let item = where[i];
        for (let j in item) {
            sql2List.push('`' + j + '`=?');
            dataList.push(item[j]);
        }
        multipleSql.push('(' + sql2List.join(' and ') + ')');
        sql2List = [];
    }

    // (`service_code`='10001' and `userid`='oVVIUvxWr0WL3d_A8_zwNnN8FlYk') or (`service_code`='10001' and `userid`='oVVIUvxWr0WL3d_A8_zwNnN8FlYk')
    sql2 = multipleSql.join(' or ');
    var sql = sql1 + 'where ' + sql2;

    this.query(sql, dataList, cb);
}

// 更新或者插入新数据 (key是data中的一部分!)
SQLHelper.prototype.pushData = function (key, data, cb) {
    this.checkExist(key, (exist) => {
        if (!exist) {
            // 不存在
            this.insert(data, (results) => {
                cb(2, results);
            });
        } else {
            // 存在记录
            this.update(key, data, (results) => {
                cb(1, results);
            });
        }
    })
}

SQLHelper.prototype.checkExist = function (whereData, cb) {
    this.select(whereData, (results) => {
        if (results.length > 0) { // 有记录
            cb(true);
        } else {
            cb(false);
        }
    })
}

SQLHelper.prototype.comments = function (cb) {
    this.query("show full columns from " + this.table, null, cb);
}

SQLHelper.prototype.count = function (cb) {
    this.query("select count(*)num from " + this.table, null, function (results) {
        cb(results[0].num);
    });
}

SQLHelper.prototype.query = function (sql, dataList, cb) {

    dbPool.getConnection(function (err, connection) {

        if (err) {

            console.log('连接池出现问题！')

        } else {

            connection.query(sql, dataList, function (error, results, fields) {
                if (error) throw error;
                if (cb) cb(results);
                connection.release();
            });

        }

    })

}


// ---------------------↓↓↓↓↓↓↓↓↓↓↓  Promise异步风格  ↓↓↓↓↓↓↓↓↓↓↓---------------------

SQLHelper.prototype.asyncQuery = function (sql, dataList) {

    var p = new Promise(function (resolve, reject) {
        dbPool.getConnection(function (err, connection) {

            if (err) {

                reject('连接池出现问题！')

            } else {

                connection.query(sql, dataList, function (error, results, fields) {
                    if (error) throw error;
                    resolve(results);
                    connection.release();
                });

            }

        })
    })

    return p;
}


SQLHelper.prototype.asyncSelect = function (whereData, offset, limit, orderby, sort) {
    // SELECT * FROM table where `score` = 1 order by `score` limite 0,10;
    var sql1 = 'SELECT * FROM ' + this.table + ' ';
    var sql2 = '';
    var sql3 = orderby ? ('order by ' + orderby + ' ' + sort + ' ') : ('');
    var sql4 = (offset > -1 && limit > 0) ? ('limit ' + offset + ',' + limit) : ('');

    var dataList = [];
    var sql2List = [];
    for (var i in whereData) {
        sql2List.push('`' + i + '` = ?');
        dataList.push(whereData[i]);
    }

    sql2 = sql2List.length ? (('where ' + sql2List.join(' and ')) + ' ') : ('');

    var sql = sql1 + sql2 + sql3 + sql4;

    return this.asyncQuery(sql, dataList);
}

module.exports = SQLHelper;